/*  ------------------------------------------------------------------------  */
/*  Paper        : Does Stakeholder Outrage Determine Executive Pay?          */
/*  Program      : a01-10-dataset.do                                          */
/*  Description  : Creating panel dataset                                     */
/*  ------------------------------------------------------------------------  */

/*  ------------------------------------------------------------------------  */
//  Start assembling dataset

/*  ------------------------------------------------------------------------  */
//  DSE Exchange dates

import sas using "$crsp/m_stock/dseexchdates.sas7bdat", clear
br
rename *, lower
format namedt nameendt %tdCCYY-NN-DD
gen nyear = year(namedt)
order nyear, after(namedt)

keep permno namedt nameendt shrcd exchcd ncusip comnam nyear siccd

capture drop sic2_ctrl
gen sic2_ctrl = floor(siccd/100)
order sic2_ctrl, after(siccd)

// 1	New York Stock Exchange
// 2	American Stock Exchange
// 3	The Nasdaq Stock Market(SM)
tab exchcd 
keep if inrange(exchcd,1,3)
tab shrcd
keep if inrange(shrcd,10,11)

tab shrcd exchcd if nyear==2020

xtset permno namedt
gen lpermno = permno

/*  ------------------------------------  */
//  Merge stock dates

merge m:1 permno using "$temp112/dsfhdr.dta"
drop if _merge==2
drop _merge
order begdat enddat, after(nameendt)

/*  ------------------------------------  */
//  Merge linking history

joinby lpermno using "$temp112/ccmxpf_lnkhist.dta", unmatched(master)
tab _merge
tab _merge, nolabel
gsort -begdat
br if _merge ==1
tab nyear _merge

keep if _merge==3
drop _merge


br permno namedt nameendt linkdt linkenddt gvkey
sort nameendt 

/*  ------------------------------------  */
//  Merge Compustat

joinby gvkey using "$temp112/funda.dta", unmatched(both)

keep if _merge==3
drop _merge
drop nyear


/*  ------------------------------------  */
//  Remove irrelevant matches

drop if datadate < linkdt
drop if datadate > linkenddt

drop if datadate < namedt
drop if datadate > nameendt

tab fyear

br permno namedt nameendt linkdt linkenddt gvkey fyear

/*  ------------------------------------  */
//  Deduplication Step 1

capture drop firmcount
by gvkey_id fyear, sort: egen firmcount = count(fyear)
	tab firmcount
	br if firmcount>1
gen primary_d = linkprim=="P"
	tab primary_d
by gvkey_id fyear, sort: egen hasprimary = max(primary_d)
	tab hasprimary
order hasprimary, after(linkprim)

//  Delete duplicates that have primary, but OBS isn't one
drop if firmcount > 1 & hasprimary==1 & primary_d==0
drop hasprimary primary_d

/*  ------------------------------------  */
//  Deduplication Step 2

capture drop firmcount
by gvkey_id fyear, sort: egen firmcount = count(fyear)
	tab firmcount
	br if firmcount>1
gen primary_d = linkprim=="C"
	tab primary_d
by gvkey_id fyear, sort: egen hasprimary = max(primary_d)
	tab hasprimary
order hasprimary, after(linkprim)

//  Delete duplicates that have primary, but OBS isn't one
drop if firmcount > 1 & hasprimary==1 & primary_d==0
drop hasprimary primary_d

/*  ------------------------------------  */
//  Deduplication check: ideally no more

capture drop firmcount
by gvkey_id fyear, sort: egen firmcount = count(fyear)
	tab firmcount
	br if firmcount>1
br

xtset gvkey_id fyear

/*  ------------------------------------  */
//  Ensure it's also a permno fyear panel

capture drop firmcount
by permno fyear, sort: egen firmcount = count(fyear)
	tab firmcount
	br if firmcount>1
by permno fyear, sort: egen max_linkenddt = max(linkenddt)
format max_linkenddt %tdCCYY-NN-DD
gen max_linkenddt_d = max_linkenddt==linkenddt
order max_linkenddt max_linkenddt_d, after(linkenddt)

//  Delete duplicates by keeping later linkenddt
drop if firmcount > 1 & max_linkenddt_d==0
drop max_linkenddt max_linkenddt_d firmcount

xtset permno fyear

order permno gvkey ncusip fyear namedt nameendt linkdt linkenddt shrcd exchcd ncusip comnam, first

//  Retain only essential variables
tab fyear
drop lpermno linkprim liid linktype lpermco
drop namedt nameendt linkdt linkenddt
drop conm shrcd exchcd
order cik comnam, after(ncusip)
order gvkey_id, after(gvkey)
order datadate, before(fyear)

save "$temp112/a112_paycut_stata.dta", replace

/*  ------------------------------------------------------------------------  */
//  BoardEx

use "$temp112/a112_paycut_stata.dta", clear
tab fyear 

//  Merge BoardEx BoardIDs
joinby ncusip using "$temp112/ncusip_boardid.dta", unmatched(master)
drop _merge
order boardid fyear, after(permno)

//  Merge BoardEx employment data
joinby boardid using "$temp112/na_dir_profile_emp.dta", unmatched(master)
gen _isNed = upper(NED)=="YES"

global isDual if _isNed ==0 & strpos(lower(RoleName), "chair") > 0 & strpos(lower(RoleName), "vice") == 0 & strpos(lower(RoleName), "division") == 0 & strpos(lower(RoleName), "region") == 0 & strpos(lower(RoleName), "deputy") == 0 & strpos(lower(RoleName), "ceo") > 0 

// tab RoleName $isDual
gen _isDual = 1 $isDual
replace _isDual = 0 if missing(_isDual)
// tab isDual

drop RowType CompanyName RoleName NED DirStart

br permno ncusip fyear datadate boardid DirectorName DirectorID DateStartRole DateEndRole

//  Directors to be included in board size measure
gen bx_include = 1 if (datadate >= DateStartRole) & (datadate <= DateEndRole) & !missing(DirectorID) & !missing(boardid)

// Calculate tenure in days
gen _dirTenureDays = datadate - DateStartRole
// Calculate difference in fractional years
gen _dirTenureYears = _dirTenureDays / 365.25
replace _dirTenureYears = . if missing(bx_include)
drop _dirTenureDays
gen _nedTenureYears = _dirTenureYears if _isNed==1

//  Board size and keep one permno fyear-boardid-observation
by boardid fyear, sort: egen boardSize = sum(bx_include)
by boardid fyear, sort: egen boardTenure = mean(_dirTenureYears)
by boardid fyear, sort: egen nedTenure = mean(_nedTenureYears)
by boardid fyear, sort: egen boardInd = mean(_isNed)
by boardid fyear, sort: egen isDual = max(_isDual)

order boardSize boardTenure boardInd isDual, after(_merge)
drop _merge DirectorName DirectorID DateStartRole DateEndRole bx_include
drop _dirTenureYears _nedTenureYears _isNed _isDual
duplicates drop permno fyear boardid, force

//  Resolve duplicates by keep boardid with larger board
by permno fyear, sort: egen _duplicate = count(fyear)
order _duplicate, first
sort permno fyear boardSize
by permno fyear, sort: egen max_board = max(boardSize)
br permno ncusip fyear datadate boardid boardSize if _duplicate > 1
drop if _duplicate>1 & boardSize !=max_board
drop _duplicate max_board

tab fyear 
xtset permno fyear

gen lnBoardSize = ln(1 + boardSize)

save "$temp112/a112_paycut_stata2.dta", replace

/*  ------------------------------------------------------------------------  */
//  BoardEx variables 

use "$temp112/a112_paycut_stata2.dta", clear
keep permno fyear datadate boardid
keep if !missing(boardid)

//  Merge BoardEx employment data
joinby boardid using "$temp112/na_dir_profile_emp.dta", unmatched(master)
tab _merge
drop _merge

//  Directors to be included in board size measure
keep if (datadate >= DateStartRole) & (datadate <= DateEndRole) & !missing(DirectorID) & !missing(boardid)

foreach var of varlist boardid RowType DirectorName CompanyName RoleName NED DateStartRole DateEndRole DirStart {
rename `var' o_`var'
}

joinby DirectorID using "$temp112/na_dir_profile_emp.dta", unmatched(master)
tab _merge
drop _merge

drop if o_boardid == boardid
keep if (datadate >= DateStartRole) & (datadate <= DateEndRole) & o_boardid != boardid
by permno fyear, sort: egen othBoardAll = count(boardid)
by permno fyear RowType, sort: egen _othBoardLst = count(boardid)
gen __othBoardLst = _othBoardLst if RowType=="Listed Organisations"
by permno fyear, sort: egen othBoardLst = max(__othBoardLst)
	replace othBoardLst = 0 if missing(othBoardLst)
	drop _othBoardLst __othBoardLst

duplicates drop permno fyear DirectorID othBoardAll othBoardLst, force
duplicates drop permno fyear DirectorID, force

//  Prior director experience
keep permno o_boardid fyear datadate DirectorID othBoardAll othBoardLst
joinby DirectorID using "$temp112/na_dir_profile_emp.dta", unmatched(master)
tab _merge
drop _merge

drop if o_boardid == boardid
drop if datadate < DateStartRole

by permno fyear DirectorID, sort: egen _priorBoards = nvals(boardid)
duplicates drop permno fyear DirectorID, force
drop RowType DirectorName CompanyName RoleName NED boardid
drop DateStartRole DateEndRole DirStart

by permno fyear, sort: egen priorBoards = sum(_priorBoards)

duplicates drop permno fyear othBoardAll othBoardLst priorBoards, force
duplicates drop permno fyear, force

keep permno fyear othBoardAll othBoardLst priorBoards

save "$temp112/a112_outsideBoard.dta", replace

/*  ------------------------------------------------------------------------  */
//  CRSP return variables

use "$temp112/a112_paycut_stata2.dta", clear

merge 1:1 permno fyear using "$temp112/a112_outsideBoard.dta"
drop _merge

br gvkey fyear datadate permno

//  Merge CRSP returns
joinby permno using "$temp112/returns.dta", unmatched(master)

tab _merge
drop _merge

global brvars gvkey fyear datadate permno ret_std exret_12m
br $brvars

gen date_delta = datadate - date if (date <= datadate)

//  Keep the return data closest to the datadate
sort permno datadate date_delta
duplicates drop permno fyear, force

xtset permno fyear

drop date date_n ret_movsum24 date_delta ret_cnt

save "$temp112/a112_paycut_stata3.dta", replace

/*  ------------------------------------  */
//  Merge firm age

use "$temp112/a112_paycut_stata3.dta", clear

joinby permno using "$temp112/firmage.dta", unmatched(master)

tab _merge
drop _merge

gen firm_age = (year(datadate) - year(namedt_start))
gen ln_firm_age = ln(1 + firm_age)
drop namedt_start

/*  ------------------------------------  */
//  Merge high and low prices

gen year = fyear
joinby permno year using "$temp112/highlow.dta", unmatched(master)

tab _merge
tab year _merge, nolabel
drop _merge

/*  ------------------------------------  */
//  Merge prior returns
 
joinby permno year using "$temp112/returns2.dta", unmatched(master)

tab _merge
tab year _merge, nolabel
drop _merge
drop year

save "$temp112/a112_paycut_stata4.dta", replace

/*  ------------------------------------  */
//  Merge ROA volatility

use "$temp112/a112_paycut_stata4.dta", clear

joinby gvkey datadate using "$temp112/roa_vol.dta", unmatched(master)

tab _merge
tab fyear _merge, nolabel
drop _merge

/*  ------------------------------------  */
//  Merge ISS governnce data

gen year = fyear
joinby ncusip year using "$temp112/rmgovernance.dta", unmatched(master)

tab _merge
tab fyear _merge, nolabel
drop _merge

xtset gvkey_id fyear
tab fyear
/*  ------------------------------------  */
//  Merge compensation data

capture drop year
gen year = fyear
merge 1:1 gvkey year using "$temp112/compensation.dta", gen(execucomp_d)

xtset gvkey_id fyear
gen ceo_change = exec_id != l1.exec_id
drop if year<=2008
drop if year>=2022
tab year execucomp_d, nolabel
drop if execucomp_d ==2
drop year 

tab fyear execucomp_d, nolabel

xtset gvkey_id fyear
gen dcut_all = (salary/l1.salary)<1
	replace dcut_all = . if exec_id != l1.exec_id
// tab fyear dcut_all if execucomp_d==3 & ceo_change ==0, nolabel
gen dcutAmount = (salary - l1.salary) / 1000
	replace dcutAmount = . if exec_id != l1.exec_id

gen dcut_5 = dcut_all
	replace dcut_5 = 0 if dcut_all==1 & (salary/l1.salary)>0.98
// tab fyear dcut_5 if execucomp_d==3 & ceo_change ==0, nolabel

gen dcut_10 = dcut_all
	replace dcut_10 = 0 if dcut_all==1 & (salary/l1.salary)>0.90
// tab fyear dcut_10 if execucomp_d==3 & ceo_change ==0, nolabel

capture drop mainsample
gen mainsample = (execucomp_d==3)*(ceo_change==0)*(fyear>2009)*(ret_cnt==12)*(missing(boardid)==0)*(year(begdat)<2019)
tab fyear mainsample
tab fyear ret_cnt if mainsample==1



/*  ------------------------------------------------------------------------  */

egen firm_id = group(gvkey)
xtset gvkey_id fyear
capture drop ceo_change
gen ceo_change = exec_id != l1.exec_id

egen firmceo_fe = group(gvkey exec_id)
order gvkey execid firmceo_fe fyear, first

by firmceo_fe, sort: gen ceo_seq = _n
// tab fyear ceo_seq
order ceo_change ceo_seq, after(fyear)

gen _mainsample_2020 = (mainsample==1)*(fyear==2020)
tab fyear _mainsample_2020
bysort firm_id: egen mainsample_2020 = max(_mainsample_2020)
drop _mainsample_2020
tab fyear mainsample_2020


/*  ------------------------------------------------------------------------  */
//  Standard control variables

capture drop sic2_ctrl
gen sic2_ctrl = floor(siccd/100)
order sic2_ctrl, after(siccd)

gen _dcut_all_2019 =1 if dcut_all==1 & fyear==2020
	replace _dcut_all_2019 = 0 if missing(_dcut_all_2019)
tab fyear _dcut_all_2019 if mainsample==1
bysort firm_id: egen dcut_firm = max(_dcut_all_2019)
order dcut_firm, after(dcut_all)
tab fyear dcut_firm if mainsample==1
drop _dcut_all_2019


gen lnBoardTenure = ln(1+boardTenure)
gen ln_ret_std = ln(ret_std)
gen ln_ceo_tenure = ln(1 + ceo_tenure)


foreach year of numlist 2019 2020 {

capture drop shrown_d_`year'
egen shrown_d_`year' = cut(shrown_tot_pct) if fyear==`year', group(4) label	
capture drop firmage_d_`year'
egen firmage_d_`year' = cut(firm_age) if fyear==`year', group(4) label	
	
}

capture drop shrown_d
	egen shrown_d = rowfirst(shrown_d_2019 shrown_d_2020)
capture drop firmage_d
	egen firmage_d = rowfirst(firmage_d_2019 firmage_d_2020)

// Board independence dummy
summ boardInd  if fyear==2020, detail
gen isBoardInd = (boardInd <= .88)

summ g_index if fyear==2020, detail
gen g_index3 = (g_index + isBoardInd + duality)
order g_index3, after(g_index)
tab g_index if fyear == 2020
tab g_index3 if fyear == 2020

summ emp oancf ivncf fincf if mainsample==1

gen isIntro = 1 if oancf<0 & ivncf<0 & fincf>=0
gen isGrowth = 1 if oancf>=0 & ivncf<0 & fincf>=0
gen isMature = 1 if oancf>=0 & ivncf<0 & fincf<0
gen isDecline = 1 if oancf<0 & ivncf>=0
gen isTransition = 1 if missing(isIntro) & missing(isGrowth) & missing(isMature) & missing(isDecline)
summ isIntro isGrowth isMature isDecline isTransition

foreach var of varlist isIntro isGrowth isMature isDecline isTransition {
	replace `var' = 0 if missing(`var')
}
summ isIntro isGrowth isMature isDecline isTransition


gen g_high = g_index >=5


foreach yvar in ret_std roa_std boardTenure nedTenure {
	replace `yvar' = 0 if missing(`yvar')
}

xtset firm_id fyear
save "$temp112/outragePanel0.dta", replace


import delimited "$datapath/insiderOwnership.csv", clear 
// br
	replace nonceoinsideownership = . if missing(insideownership)
	by permno, sort: egen _cnt = count(permno)
	gsort -_cnt permno
	keep permno insideownership ceoownership nonceoinsideownership
	duplicates drop *, force
	sort nonceoinsideownership
save "$temp112/insiderOwnership.dta", replace

use "$temp112/outragePanel0.dta", clear

joinby firm_id using "$temp112/instoVariables.dta", unmatched(master)
	tab _merge
	drop _merge

sort cik
// br if inrange(fyear,2019,2020) & mainsample==1

gen issuerCIK = cik

merge m:1 issuerCIK using "$temp112/FinalForm3.dta"
	drop if _merge==2
	drop _merge issuerCIK

gen subjectCik = cik

merge m:1 subjectCik using "$temp112/activists.dta"
	drop if _merge==2
	drop _merge

merge m:1 subjectCik using "$temp112/proxies.dta"
	drop if _merge==2
	drop _merge
merge m:1 subjectCik using "$temp112/proxiesInsto.dta"
	drop if _merge==2
	drop _merge subjectCik

merge m:1 permno using "$temp112/insiderOwnership.dta"
	drop if _merge==2
	drop _merge 

foreach var of varlist f3_filings f3_owners f3_NIfilings f3_NIowners actFilings actOwners proxyFilings proxyOwners proxyFilingsInsto nonceoinsideownership insideownership instoHhi instoChurn {
replace `var' = 0 if missing(`var')
}


xtset firm_id fyear

foreach var of varlist _all {
	label var `var' ""
}

save "$temp112/outragePanel.dta", replace

/*  -|----------------------------------------------------------------------  */
/*  ||  ---    Program end: a01-10-dataset.do                                 */
/*  -|  ---    Attila Balogh    --------------------------------------------  */